"""add_import_records_table

Revision ID: 0a47211d84db
Revises: 20241002_analysis_scoring
Create Date: 2025-10-06 04:02:13.264187

"""
# pylint: skip-file
# type: ignore
# pyright: reportAttributeAccessIssue=false, reportGeneralTypeIssues=false
from typing import Sequence, Union

from alembic import op  # type: ignore[import]
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '0a47211d84db'
down_revision: Union[str, Sequence[str], None] = '20241002_analysis_scoring'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('import_records',  # type: ignore
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('import_batch_id', sa.UUID(), nullable=False),
    sa.Column('original_data', sa.JSON(), nullable=False, comment='Original Excel/CSV row: {name, phone, area, remark, etc}'),
    sa.Column('normalized_data', sa.JSON(), nullable=False, comment='Standardized fields: {name_norm, phone_norm, area_norm}'),
    sa.Column('ai_tags', sa.JSON(), nullable=True, comment='AI-generated tags: [{name, confidence, source}]'),
    sa.Column('duplicate_candidates', sa.JSON(), nullable=True, comment='Potential duplicates: [{record_id, score, match_type}]'),
    sa.Column('confidence', sa.Float(), nullable=False, comment='Overall confidence score 0.0-1.0'),
    sa.Column('status', sa.String(length=20), nullable=False, comment='pending/confirmed/merged/rejected'),
    sa.Column('review_notes', sa.Text(), nullable=True, comment='Manual review notes from admin'),
    sa.Column('finalized', sa.Boolean(), nullable=False, comment='Whether written to leads table'),
    sa.Column('finalized_lead_id', sa.Integer(), nullable=True, comment='ID of created lead after finalization'),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['import_batch_id'], ['importbatch.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_import_records_finalized'), 'import_records', ['finalized'], unique=False)  # type: ignore
    op.create_index(op.f('ix_import_records_finalized_lead_id'), 'import_records', ['finalized_lead_id'], unique=False)  # type: ignore
    op.create_index(op.f('ix_import_records_id'), 'import_records', ['id'], unique=False)  # type: ignore
    op.create_index(op.f('ix_import_records_import_batch_id'), 'import_records', ['import_batch_id'], unique=False)  # type: ignore
    op.create_index(op.f('ix_import_records_status'), 'import_records', ['status'], unique=False)  # type: ignore
    # ### end Alembic commands ###

def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_import_records_status'), table_name='import_records')  # type: ignore
    op.drop_index(op.f('ix_import_records_import_batch_id'), table_name='import_records')  # type: ignore
    op.drop_index(op.f('ix_import_records_id'), table_name='import_records')  # type: ignore
    op.drop_index(op.f('ix_import_records_finalized_lead_id'), table_name='import_records')  # type: ignore
    op.drop_index(op.f('ix_import_records_finalized'), table_name='import_records')  # type: ignore
    op.drop_table('import_records')  # type: ignore
    # ### end Alembic commands ###
